/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.informet.ipd.modelo.dao;

import com.informet.ipd.modelo.vo.CearVO;
import com.informet.ipd.modelo.vo.CrecerConDeporteVO;
import com.informet.ipd.modelo.vo.ProgEstrVO;
import com.informet.ipd.modelo.vo.ProyectosInversionVO;
import com.informet.ipd.modelo.vo.RolUsuarioVO;
import com.informet.ipd.modelo.vo.TalentoVO;
import com.informet.ipd.modelo.vo.UbigeoVO;
import com.informet.ipd.modelo.vo.UsuarioVO;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class CearAlumnoDao extends DBase{


  Connection conn = null;

    public CearAlumnoDao() {
        super();
    }

    public void  setListPrueba(CearVO bean, ResultSet rs) throws SQLException{
          bean.setCodPrueba(rs.getString("NUM_CODprueba_PK"));
          bean.setDescPrueba(rs.getString("TX_DESCprueba"));

    }

    public void  setListCearAlumno(CearVO bean, ResultSet rs) throws SQLException{
          
          bean.setCodCear(rs.getString("num_cear_pk"));
          bean.setCodAlumno(rs.getString("NUM_codalumcear_pk"));
          bean.setNombre(rs.getString("tx_nombrecear"));
          bean.setApePaterno(rs.getString("TX_apaternocear"));
          bean.setApeMaterno(rs.getString("TX_amaternoCEAR"));
          bean.setFecNacimiento(rs.getString("dt_nacimientoCEAR"));
          bean.setCondicion(rs.getString("NUM_condicionCEAR"));
          //ubigeo
          bean.setCodNivel(rs.getString("num_nivelinicear"));
          bean.setCodDisciplina(rs.getString("NUM_CODISCCEAR"));
          bean.setCodCategoria(rs.getString("NUM_CODcategoria"));
          bean.setEdad(rs.getString("edad"));


          //bean.setDescDisciplina(usuario);
          if(bean.getCodNivel().equals("1"))
            bean.setDescNivel("DP");
          if(bean.getCodNivel().equals("2"))
            bean.setDescNivel("DC");
          if(bean.getCodNivel().equals("3"))
            bean.setDescNivel("DCAN");

          if(bean.getCondicion().equals("1"))
            bean.setDescCondicion("Nuevo Ingreso");
          if(bean.getCondicion().equals("2"))
            bean.setDescCondicion("Continua");

          bean.setCodCausaRetiro(rs.getString("NUM_CODCAUSARETIRO_PK"));

    }



     public List getlistPrueba(String idPerfil,String codDisc) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<CearVO> lista = new ArrayList<CearVO>(0);
        //List lista=null;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select * from me_pruebacear p where 1=1"+
                  " and p.NUM_CODDISC_PK="+codDisc;

            if(idPerfil!=null)
            sql=sql+"  and num_codperfil_pk="+idPerfil;

            
            rs = stmt.executeQuery(sql);
            CearVO bean = null;
            while (rs.next()) {
                bean = new CearVO();
               setListPrueba(bean, rs);

               lista.add(bean);

            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

 
public List getListAlumno(String codCear,String codUser,String nombre, String paterno,
                          String materno,String condicion,String nivel,String disciplina, String role) throws SQLException {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<CearVO> lista = new ArrayList<CearVO>(0);        
        String sql = "";        
        try {
            conn = DriverManager.getConnection(url, usuario, password);            
            stmt = conn.createStatement();
            sql = " select "+
                  " datediff(yy,dt_nacimientocear,getdate())as edad, "+
                  " isnull(NUM_CODCAUSARETIRO_PK,'') as NUM_CODCAUSARETIRO_PK, "+

                  " isnull(convert(varchar,dt_nacimientocear,103),'')as dt_nacimientocear, "+

                  "  *  "+
                  " from me_cear mc inner join me_alumnoscear a on mc.num_cear_pk=a.num_cear_pk "+
                  
                  "  where 1=1  "+
                  
                  "   and mc.num_aniocear=datepart(yy,getdate())+1 "+
                "   and mc.num_cear_pk="+codCear;
            
            if(role.equals("PROFESOR"))
                sql =sql+ " and mc.NUM_CODUSER="+codUser;

            if(!nombre.equals(""))
                sql =sql+ " and a.tx_nombrecear like '%"+nombre+"%'";

            if(!paterno.equals(""))
                sql =sql+ " and a.TX_APATERNOCEAR like '%"+paterno+"%'";

            if(!materno.equals(""))
                sql =sql+ " and a.TX_AMATERNOCEAR like '%"+materno+"%'";

            if(!condicion.equals("-1"))
                sql =sql+ " and a.NUM_CONDICIONCEAR="+condicion;

            if(!nivel.equals("-1"))
                sql =sql+ " and a.NUM_NIVELINICEAR="+nivel;

            if(!disciplina.equals("-1"))
                sql =sql+ " and a.NUM_CODISCCEAR="+disciplina;

            rs = stmt.executeQuery(sql);
            CearVO bean1 = null;
            while (rs.next()) {
                bean1 = new CearVO();
                setListCearAlumno(bean1, rs);
                lista.add(bean1);
            }
           
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
      // JOptionPane.showMessageDialog(null, lista.size());
        return lista;
    }
       

      public String spMan(CearVO bean) {
        String msj = "";
        //String codP=bean.getCodPrograma();
        try {/*         
                    fecresultado,marfecresultadocaNacional,recordNacional,rankingSudamericano,codCausaRetiro
                    codResultado,codtrimestre,marcaresultado
              */
        	
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_UPDATE_CEARALUMNO(?,?,?,?,?,?,?,?,?,?,?)}");
            cs.setString(1, bean.getCodAlumno());
            cs.setString(2, bean.getApePaterno());
            cs.setString(3, bean.getApeMaterno());
            cs.setString(4, bean.getNombre());
            cs.setString(5, bean.getFecNacimiento());
            cs.setString(6, bean.getCondicion());
            cs.setString(7, bean.getCodNivel());
            cs.setString(8, bean.getCodDisciplina());
            cs.setString(9, bean.getCodCear());
            cs.setString(10, bean.getTipoOper());
            cs.setString(11, bean.getCodCategoria());
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }

}
